import type { Router } from "express";
import type { Ioracle, IcallbackFn } from "../../types";
import { decrypt } from "../../utils/privateKey";
import { BindParameters } from "oracledb";
import { sendMail } from "./mail";

interface Header {
  username: string;
  docid: string;
  operation: string;
  comments: string;
  manager_flag: string;
}

interface Data {
  assessment_result: string;
  assessment_score: string;
  actual_start_date: string;
  actual_end_date: string;
  unit_code: string;
}

const Maintaince = (
  router: Router,
  oracle: Ioracle,
  callbackFn: IcallbackFn
) => {
  router.post("/getdisposal", (req, res) => {
    let { username, querytype, current, pagesize } = req.body;
    username = decrypt(username);
    let sql = "";
    if (querytype === "Count") {
      sql = ` select count(*)count from xxcc_new_engineercourse_header a,xxcc_new_engineercourse_flow b
         where a.docid = b.docid and a.status = 'Y' and a.current_flow = b.flow and b.action_klm = '${username}'`;
      callbackFn(res, oracle.connHrm, { sql });
    } else {
      sql = `select * from (select row_number() over(order by c.docid) as rowseq,c.* from(
               select a.docid,b.phase_code,a.engineer_badge,a.engineer_name from xxcc_new_engineercourse_header a,xxcc_new_engineercourse_flow b
               where a.docid = b.docid and a.status = 'Y' and a.current_flow = b.flow and b.action_klm = '${username}') c
            ) where rowseq between ${(current - 1) * pagesize + 1} and ${
        current * pagesize
      }`;
      callbackFn(res, oracle.connHrm, { sql });
    }
  });

  router.post("/getdetail", (req, res) => {
    const { docid, username } = req.body;
    let sql = `select a.docid,a.engineer_dept,a.engineer_name,a.manager_dept,a.manager_name,b.unit_code,b.objective
        ,b.teacher_dept,b.teacher_name,b.teacher_badge,b.train_hours,b.trian_instructoin,to_char(b.start_date,'yyyy-mm-dd')start_date
        ,to_char(a.engineer_indate,'yyyy-mm-dd')engineer_indate,to_char(a.created_date,'yyyy-mm-dd')created_date
        ,to_char(b.end_date,'yyyy-mm-dd')end_date,b.assessment_method,c.phase_code
        ,case when d.account = '${decrypt(
          username
        )}' then 'Y' else 'N' end as manager_flag,assessment_result,assessment_score,to_char(actual_start_date,'yyyy-mm-dd')actual_start_date
        ,to_char(actual_end_date,'yyyy-mm-dd') actual_end_date
        from xxcc_new_engineercourse_header a,xxcc_new_engineercourse_lines b,xxcc_new_engineercourse_flow c,user_info_n d
        where a.docid = b.docid and a.docid = c.docid and a.current_flow = c.flow and a.docid='${docid}' and a.manager_badge = d.work_num`;
    callbackFn(res, oracle.connHrm, { sql });
  });

  router.post("/approve", async (req, res) => {
    const { header, data } = req.body;
    const headerParse: Header = JSON.parse(header);
    const dataParse: Data[] = [];
    JSON.parse(data).forEach((element: Data) => {
      dataParse.push(element);
    });
    let sql = "";
    if (headerParse.manager_flag === "Y" && headerParse.operation === 'approve') {
      sql = `update xxcc_new_engineercourse_lines set assessment_result = :assessment_result,assessment_score=:assessment_score
          ,actual_start_date=to_date(:actual_start_date,'yyyy-mm-dd'),actual_end_date=to_date(:actual_end_date,'yyyy-mm-dd')
          where docid=:docid and unit_code = :unit_code`;
      let bind: Array<BindParameters> = [];
      for (let i = 0; i < dataParse.length; i++) {
        bind.push({ docid: headerParse.docid, ...dataParse[i] });
      }
      try {
        await oracle.connHrm({ sql, isMulti: true, params: bind });
      } catch  {
        res.send({
          code: 0,
          message: "操作失敗，請稍後重試或聯繫管理員",
        });
        return 
      }
    }
    sql = `begin xxcc_new_engineer_approve('${
      headerParse.operation
    }','${decrypt(headerParse.username)}','${headerParse.docid}','${
      headerParse.comments
    }');end;`;
    try {
      await oracle.connHrm({ sql });
      sql = `select  c.chinese_name,substr(c.account,9) || '@compeq.huizhou.gd.cn' email,a.status,
             substr(a.created_by,9) || '@compeq.huizhou.gd.cn' create_email  
            from xxcc_new_engineercourse_header a,xxcc_new_engineercourse_flow b,user_info_n c
            where a.docid = b.docid and a.current_flow = b.flow and b.action_klm = c.account and a.docid = '${headerParse.docid}'`;
      const resultEmail = await oracle.connHrm({ sql });
      const chinese_name = resultEmail.data[0].chinese_name as string;
      const email = resultEmail.data[0].email as string;
      const status = resultEmail.data[0].status as string;
      const creatoremail = resultEmail.data[0].create_email as string;
      if (status === "Y") {
        await sendMail({
          mailTo: email,
          mailSubject:
            headerParse.operation === "approve"
              ? "新進工程師課級訓練系統-簽核通知郵件"
              : "新進工程師課級訓練系統-退回通知郵件",
          mailHtml:
            headerParse.operation === "approve"
              ? "./mail/newengineer/send_approve.ejs"
              : "./mail/newengineer/send_reject.ejs",
          mailData: { chinese_name, email },
        });
        res.send({ code: 200, data: [], message: "success" });
      } else {
        await sendMail({
          mailTo: creatoremail,
          mailSubject:"新進工程師課級訓練系統-簽核完成郵件",
          mailHtml:"./mail/newengineer/send_complete.ejs",
          mailData: { chinese_name, creatoremail },
        });
        res.send({ code: 200, data: [], message: "success" });
      }
    } catch {
      res.send({
        code: 0,
        message: "操作完成，郵件發送異常。請聯繫管理員或手動發送郵件",
      });
    }
  });

  return router;
};

export default Maintaince;
